Nested IF statements in Excel [Over the 7 allowed limit]

Posted by Alks on Stack Overflow See other posts from Stack Overflow or by Alks
Published on 2010-03-17T19:42:46Z Indexed on 2010/03/17 19:51 UTC
Read the original article Hit count: 189

Filed under:

excel

|

nested

|

if

|

statement

|

vba

hey guys, i am trying to create a spreadsheet which automagically gives a grade to a student based on their marks they got.

I've apparently hit excels nested IF statement limit which is 7.

here's my if statement:

=IF(O5>0.895,"A+",IF(O5>0.845,"A",IF(O5>0.795,"A-",IF(O5>0.745,"B+",IF(O5>0.695,"B",IF(O5>0.645,"B-",IF(O5>0.595,"C+",IF(O5>0.545,"C","D"))))))))

I was reading online that I could create a VBA script and assign it that, but I dont know anything about VBA....so if someone could help me write a VBA for this, would be awesome.

Its still mising the C- grade and anything lower should be awarded a D mark.

This is the grading scheme I am trying to create...:

A+ 89.500 - 100.000 Pass with Distinction

A 84.500 - 89.490 Pass with Distinction

A- 79.500 - 84.490 Pass with Distinction

B+ 74.500 - 79.490 Pass with Merit

B 69.500 - 74.490 Pass with Merit

B- 64.500 - 69.490 Pass with Merit

C+ 59.500 - 64.490 Pass

C 54.500 - 59.490 Pass

C- 49.500 - 54.490 Pass

D 0.000 - 49.490 Specified Fail

I wouldn't mind going down the VBA route, however my understanding of VB language is absolutely minimal (don't like it)...if this gets too tedious, I was thinking to create a small php/mysql application instead.

Cheers :)

© Stack Overflow or respective owner

Related posts about excel

Excel error "This workbook contains Excel 4.0 macros or Excel 5.0 modules"

as seen on Super User - Search for 'Super User'
I have a workbook that was protected via the Protect Workbook feature. It was sent to someone else to modify. When they sent it back, it was unprotected and when I try to reprotect it I get this error, "This workbook contains Excel 4.0 macros or Excel 5.0 modules. If you would like to… >>> More
Open excel 2007 excel files and save as 97-2003 formats in VBA

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a weird situation where I have a set of excel files, all having the extension .xls., in a directory where I can open all of them just fine in Excel 2007. The odd thing is that I cannot open them in Excel 2003, on the same machine, without opening the file first in 2007 and going and saving… >>> More
Creating Excel or Excel compatible Spreadsheets on the server side in C#

as seen on Stack Overflow - Search for 'Stack Overflow'
I'd like to make server-side excel compatible spreadsheets that maybe use OpenXML or a structured data format. I've used Office Interop before to generate Excel spreadsheets, but those apps run on a PC that has office installed. For this web project I'm building, the server doesn't have office installed… >>> More
vba access values from excel workbook from another excel workbook

as seen on Stack Overflow - Search for 'Stack Overflow'
Any ideas why this code will not work? Workbooks.Open Filename:="C:\a.xls" Workbooks("a.xls").Activate Worksheets("b").Select Dim OutputArray(10, 10) OutputArray(1, 1) = Workbooks("c.xls").Worksheets("worksheet_otherthan_default").Select.Range(A1).Value I'm trying to run a macro in one file (unnamed… >>> More
Excel workbook event order and usage when closing Excel

as seen on Stack Overflow - Search for 'Stack Overflow'
Given the following workbook events: BeforeClose BeforeSave Please tell me: - The firing order in the case of multiple workbooks alreay opened (wb1, wb2 and wb3 are opened in this order) and the user closes Excel. You can assume all 3 needs saving. - What happen if user cancels one of the saving… >>> More

Related posts about nested

Nested class or not nested class?

as seen on Stack Overflow - Search for 'Stack Overflow'
I have class A and list of A objects. A has a function f that should be executed every X seconds (for the first instance every 1 second, for the seconds instance every 5 seconds, etc.). I have a scheduler class that is responsible to execute the functions at the correct time. What i thought to do… >>> More
Nested class - calling the nested class from the parent class

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a class whereby a method calls a nested class. I want to access the parent class properties from within the nested class. public class ParentClass { private x; private y; private z; something.something = new ChildClass public class ChildClass { need to get x, y and z; } } How do I… >>> More
How Do I Prevent Rails From Treating Updated Nested Attributes Differently From New Nested Attribute

as seen on Stack Overflow - Search for 'Stack Overflow'
I am using rails3 beta3 and couchdb via couchrest. I am not using active record. I want to add multiple "Sections" to a "Guide" and add and remove sections dynamically via a little javascript. I have looked at all the screencasts by Ryan Bates and they have helped immensely. The only difference… >>> More
Created nested model setting a property on nested model before save

as seen on Stack Overflow - Search for 'Stack Overflow'
I have two models a Company and a User the Company has_many :users and the User belongs_to :company. I have a form such as: <%= form_for @company, data: {toggle: :validator}, novalidate: "novalidate", html: {role: :form} do |f| %> company fields Then in there I have <%= f.fields_for… >>> More
Sorting deeply nested attributes in Rails

as seen on Stack Overflow - Search for 'Stack Overflow'
I want to be able to drag and drag App model which is nested under Category model. http://railscasts.com/episodes/196-nested-model-form-part-1 's the Railscast I've tried to follow. Category controller def move params[:apps].each_with_index do |id, index| Category.last.apps.update(['position=… >>> More